const { Sequelize, DataTypes } = require("sequelize");

// 从环境变量中读取数据库配置
const MYSQL_USERNAME = 'root';
const MYSQL_PASSWORD = '123456';
const host = 'localhost';
const port = '3306';
const databaseName = 'smart_recipes';

// 先创建一个不指定数据库的 Sequelize 实例用于创建数据库
const tempSequelize = new Sequelize({
  host: host,
  port: port,
  username: MYSQL_USERNAME,
  password: MYSQL_PASSWORD,
  dialect: "mysql"
});

// 创建数据库
async function createDatabase() {
  try {
    await tempSequelize.query(`CREATE DATABASE IF NOT EXISTS ${databaseName}`);
    console.log('数据库创建成功');
  } catch (error) {
    console.error('创建数据库时出错:', error);
  }
}

// 创建指定数据库的 Sequelize 实例
const sequelize = new Sequelize(databaseName, MYSQL_USERNAME, MYSQL_PASSWORD, {
  host: host,
  port: port,
  dialect: "mysql"
});

// 定义用户表模型
const User = sequelize.define("User", {
  username: {
    type: DataTypes.STRING(255),
    allowNull: false,
    unique: true,
    comment: '用户的唯一用户名'
  },
  password: {
    type: DataTypes.STRING(255),
    allowNull: false,
    comment: '用户的密码'
  },
  role: {
    type: DataTypes.STRING,
    allowNull: false,
    defaultValue: 'user',
    comment: '用户角色，默认为"user"'
  },
  updated_at: {
    type: DataTypes.DATE,
    defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
    onUpdate: Sequelize.literal('CURRENT_TIMESTAMP'),
    comment: '用户信息最后更新时间，自动更新'
  }
}, {
  timestamps: false,
  comment: '存储用户基本信息'
});

// 定义食谱主表模型
const Recipes = sequelize.define("Recipes", {
  name: {
    type: DataTypes.STRING(255),
    allowNull: false,
    comment: '食谱名称，如"番茄炒蛋"'
  },
  image_url: {
    type: DataTypes.STRING(255),
    comment: '食谱封面图片URL地址'
  },
  description: {
    type: DataTypes.TEXT,
    comment: '食谱的详细描述或故事背景'
  },
  prep_time: {
    type: DataTypes.INTEGER,
    comment: '准备时间(分钟)'
  },
  cook_time: {
    type: DataTypes.INTEGER,
    comment: '烹饪时间(分钟)'
  },
  servings: {
    type: DataTypes.INTEGER,
    comment: '可供多少人食用'
  },
  created_at: {
    type: DataTypes.DATE,
    defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
    comment: '记录创建时间'
  },
  updated_at: {
    type: DataTypes.DATE,
    defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
    comment: '最后更新时间，自动更新'
  }
}, {
  timestamps: false,
  comment: '存储食谱主体信息'
});

// 定义食材表模型
const Ingredients = sequelize.define("Ingredients", {
  name: {
    type: DataTypes.STRING(255),
    allowNull: false,
    comment: '食材名称，如"番茄"'
  },
  amount: {
    type: DataTypes.STRING(100),
    comment: '食材用量，如"2"'
  },
  unit: {
    type: DataTypes.STRING(50),
    comment: '食材单位，如"个"、"克"'
  }
}, {
  timestamps: false,
  comment: '存储食谱所需的食材清单'
});

// 定义烹饪步骤表模型
const Steps = sequelize.define("Steps", {
  step_number: {
    type: DataTypes.INTEGER,
    allowNull: false,
    comment: '步骤序号,从1开始'
  },
  instruction: {
    type: DataTypes.TEXT,
    allowNull: false,
    comment: '步骤详细说明'
  }
}, {
  timestamps: false,
  comment: '存储食谱的详细制作步骤'
});

// 定义营养信息表模型
const Nutrition = sequelize.define("Nutrition", {
  calories: {
    type: DataTypes.INTEGER,
    comment: '卡路里含量(千卡)'
  },
  protein: {
    type: DataTypes.DECIMAL(10, 2),
    comment: '蛋白质含量(克)'
  },
  fat: {
    type: DataTypes.DECIMAL(10, 2),
    comment: '脂肪含量(克)'
  },
  carbs: {
    type: DataTypes.DECIMAL(10, 2),
    comment: '碳水化合物含量(克)'
  }
}, {
  timestamps: false,
  comment: '存储食谱的营养成分信息'
});

// 建立关联关系
Recipes.hasMany(Ingredients, {
  foreignKey: 'recipe_id',
  onDelete: 'CASCADE'
});
Ingredients.belongsTo(Recipes, {
  foreignKey: 'recipe_id'
});

Recipes.hasMany(Steps, {
  foreignKey: 'recipe_id',
  onDelete: 'CASCADE'
});
Steps.belongsTo(Recipes, {
  foreignKey: 'recipe_id'
});
Steps.addHook('beforeCreate', async (step) => {
  const maxStep = await Steps.max('step_number', {
    where: { recipe_id: step.recipe_id }
  });
  step.step_number = maxStep ? maxStep + 1 : 1;
});

Recipes.hasOne(Nutrition, {
  foreignKey: 'recipe_id',
  onDelete: 'CASCADE'
});
Nutrition.belongsTo(Recipes, {
  foreignKey: 'recipe_id'
});

// 建立用户与食谱的关联关系
User.hasMany(Recipes, {
  foreignKey: 'user_id',
  onDelete: 'CASCADE'
});
Recipes.belongsTo(User, {
  foreignKey: 'user_id'
});

// 初始化数据库
async function init() {
  try {
    await createDatabase();
    await sequelize.authenticate();
    console.log('数据库连接成功');

    // 同步模型到数据库
    await User.sync({ alter: true });
    await Recipes.sync({ alter: true });
    await Ingredients.sync({ alter: true });
    await Steps.sync({ alter: true });
    await Nutrition.sync({ alter: true });

    console.log('数据库和表创建成功');
  } catch (error) {
    console.error('初始化数据库时出错:', error);
  }
}

// 导出 Sequelize 实例和模型
module.exports = {
  init,
  sequelize,
  User,
  Recipes,
  Ingredients,
  Steps,
  Nutrition
};